﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Dapper;
using DapperDemo_One.lib;
using DapperDemo_One.Models;

namespace DapperDemo_One.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            using (var connection = conn工厂.GetOpenConnection())
            {
                var sql = "select a.Id,a.name  as name,b.id AS tid,b.name AS tname ,c.Id AS thid,c.name AS thname FROM one a left join two b on a.id=b.oneid LEFT JOIN dbo.Three c ON c.twoid=b.Id";

                var lookup = new Dictionary<int, One>();
                var query = connection.QueryAsync<One, Two, Three, One>(sql, (a, b, c) =>
                  {
                      One tmp;
                      if (!lookup.TryGetValue(a.Id, out tmp))
                      {
                          tmp = a;
                          lookup.Add(a.Id, tmp);
                      }

                      var tmptow = tmp.Tows.Find(f => f.tid == b.tid);
                      if (tmptow == null)
                      {
                          tmptow = b;
                          tmp.Tows.Add(tmptow);
                      }
                      if (c != null)
                      {
                          tmptow?.Threes.Add(c);
                      }
                      return a;
                  }, splitOn: "tid,thid");
                var lst = lookup.Values.ToList();
                var arr = query.Result.ToArray();
                //arr.IsSequenceEqualTo(new[] { "abc", "def" });
            }
            return View();
        }

        public ActionResult About()
        {
            using (var connection = conn工厂.GetOpenConnection())
            {
                var sql = "select a.Id,a.name  as name,b.id AS tid,b.name AS tname  FROM one a left join two b on a.id=b.oneid";

                var lookup = new List<One>();
                var query = connection.QueryAsync<One, Two, One>(sql, (a, b) =>
               {
                   One tmp = lookup.Find(f => f.Id == a.Id);
                   if (tmp == null)
                   {
                       tmp = a;
                       lookup.Add(tmp);
                   }
                   tmp.Tows.Add(b);
                   return a;
               }, splitOn: "tid");
                var lst = lookup;
                var arr = query.Result.ToArray();
                //arr.IsSequenceEqualTo(new[] { "abc", "def" });
            }

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }

        #region 事务

        public void test3()
        {
            using (var connection = conn工厂.GetOpenConnection())
            {
                IDbTransaction transaction = connection.BeginTransaction();
                try
                {
                    var now = DateTime.Now;
                    string query = "INSERT INTO dbo.DingDan(shid, czid, cjsj, sjsj, zt, cjrzh,jine)VALUES(1, 1, '" + now + "','" + now + "',1, 'qwe',0); SELECT SCOPE_IDENTITY();";
                    var ddid/*订单Id*/ = Convert.ToInt32(connection.ExecuteScalar(query, new { }, transaction, null, null));
                    foreach (var item in new List<IEnumerable<KeyValuePair<int, Dictionary<int, string>>>>())
                    {
                        foreach (KeyValuePair<int, Dictionary<int, string>> keyValuePair in item)
                        {
                            query = "INSERT INTO dbo.DDDetails(DDId, cpId)VALUES(" + ddid + "," + keyValuePair.Key + "); SELECT SCOPE_IDENTITY();";
                            var dddid/*订单详细Id*/ = Convert.ToInt32(connection.ExecuteScalar(query, new { }, transaction, null, null));
                            var updateddinfo =
                                "UPDATE dbo.DingDan SET jine=jine+(SELECT dj FROM dbo.Lottery_Main WHERE id=" + keyValuePair.Key + ") WHERE id=" + ddid;
                            connection.ExecuteScalar(updateddinfo, new { }, transaction, null, null);
                            foreach (KeyValuePair<int, string> valuePair in keyValuePair.Value)
                            {
                                query = "INSERT INTO dbo.DDDetails_son(DDDId, gzId, nr)VALUES(" + dddid + "," + valuePair.Key + ",'" + valuePair.Value + "')";
                                connection.Execute(query, new { }, transaction, null, null);
                            }
                        }

                    }
                    //提交事务
                    transaction.Commit();
                    var result = ddid;
                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    transaction.Rollback();
                    throw new Exception(ex.Message);

                }
            }
        }
        #endregion

        #region 存储过程

        public void test2()
        {
            using (var connection = conn工厂.GetOpenConnection())
            {
                int 记录数;
                var param = new DynamicParameters();
                param.Add("@pageIndex", 1);
                param.Add("@pagesize", 10);
                param.Add("@keyword", "123");
                param.Add("@record", 0, DbType.Int32, ParameterDirection.Output);
                var res2 = connection.Query<One>("GetDepartmentList", param, null, true, null,
                    CommandType.StoredProcedure); //res2.Count = 80
                int res = param.Get<int>("@record"); //获取数据库输出的值
                记录数 = res;
                var result = res2.ToList();
            }
        }

        #endregion

        #region SQL_有返回值
        /// <summary>
        /// 返回单条信息
        /// </summary>
        public void test1()
        {
            using (var connection = conn工厂.GetOpenConnection())
            {
                string query = "SELECT * FROM dbo.Department WHERE Id=@id";
                var result = connection.Query<One>(query, new { id = 1 }).SingleOrDefault();
            }
        }


        #endregion

        #region SQL_无返回值

        public void test4()
        {
            using (var connection = conn工厂.GetOpenConnection())
            {
                var updateddinfo =
                    "UPDATE dbo.DingDan SET jine=jine+(SELECT dj FROM dbo.Lottery_Main WHERE id=" + 1 + ") WHERE id=" +
                    1;
                connection.ExecuteScalar(updateddinfo, new { }, null, null, null);
                //connection.Execute(updateddinfo, null, null, null, null);
            }
        }


        #endregion
    }
}